################################################################################
# Transform XML files into a database-friendly format
# Usage: perl -CS "Insert Data.pl"
# (-CS is necessary for handling UTF8 characters)
#
# Run the insert statements from SQL*Plus, and run the CSV files from SQL Loader.
#
# Created by Jon Heller (jonearles@yahoo.com)
################################################################################


#These are the commands I used to get XML::LibXML working:
#ppm install http://cpan.uwinnipeg.ca/PPMPackages/10xx/XML-LibXML-Common.ppd
#ppm install http://cpan.uwinnipeg.ca/PPMPackages/10xx/XML-NamespaceSupport.ppd
#ppm install http://cpan.uwinnipeg.ca/PPMPackages/10xx/XML-LibXML.ppd

use strict;
use XML::LibXML;
use File::Find;
use HTML::Entities ();
use Encode;


#All files will be stored in subfolders in this directory.
my $baseDirectory = "D:/Kiva/";

my $loanDirectory = $baseDirectory."xml/loans/";

#Don't parse duplicate loans.  Duplicate loans are always in order, so only
#the last ID needs to be checked.
my $previousLoanID = -999;

my (@loanInserts, @paymentCSV, @localPaymentCSV, @scheduledPaymentCSV
  ,@partnerInserts, @countryInserts, @languageCSV, @borrowerInserts);

#Find, parse, and convert all XML files into insert statements
find(\&parseLoan, ($loanDirectory));
parsePartners($baseDirectory."xml/partners.xml");


#Print the insert statements
#TODO: This will generate very large text files.  Should I break the process up?
printInserts();



################################################################################
# Convert Date to Oracle format
# Input 1: A string in the format 2009-01-31T08:00:00Z
# Returns: to_date('2009-01-31T08:00:00Z', 'YYYY-MM-DD HH24:MI:SS')
################################################################################
sub convertDateToOracleFormat
{
  #Replace the 'T' with ' ', and remove the 'Z'.
  $_[0] =~ s/T/ /;
  $_[0] =~ s/Z//;

  #If the nls_date_format is set correctly no to_date is needed, just ''
  #$_[0] = "'$_[0]'";  
  #Add the to_date information
  #$_[0] = "to_date('".$_[0]."')";
  #with date format model
  #$_[0] = "to_date('".$_[0]."', 'YYYY-MM-DD HH24:MI:SS')";
 
  return $_[0];
}


################################################################################
# Convert the string to a Oracle friendly format
#
# Replace HTML codes with actual characters
# Replace any apostrophes with two apostrophes
# Remove any newlines at end.
################################################################################
sub convertStringToOracleFormat
{
  $_[0] = HTML::Entities::decode($_[0]);
  $_[0] =~ s/'/''/g;
  #Will still include the windows character char(10), but I don't think it matters.
  #chomp($_[0]);
  
  #Remove all newlines
  $_[0] =~ s/\n//g;
  $_[0] =~ s/\r//g;
  
  return $_[0];
}




################################################################################
# Convert Number to Oracle Format
# Change empty value to null
################################################################################
sub convertNumberToOracleFormat
{
  if($_[0] eq '')
  {
    return "null";
  }
  else
  {
    return $_[0];
  }
}




################################################################################
# getFirstChildData
# Inputs: An array of elements
# Returns: The data of the first element if it exists, else an empty string
################################################################################
sub getFirstChildData
{
  if(@_ == 0 or $_[0] eq '' or $_[0]->getFirstChild eq '')
  {
    return '';
  }
  else
  {
    return $_[0]->getFirstChild->getData;
  }
}




################################################################################
# Parse Loan
# Input 1: file name
################################################################################
sub parseLoan
{
  my $fileName = $_;

  #Process any files that end in .xml
  if (lc($fileName) =~ /.*\.xml$/)

  #(1000 values)
  #if (lc($fileName) =~ /^801[0-9][0-9]\.xml$/)
  #(This range has UTF8 chracters)
  #if (lc($fileName) =~ /^802[0-9][0-9]\.xml$/)
  #(This range has html characters)
  #if (lc($fileName) =~ /^805[0-9][0-9]\.xml$/)
  #if (lc($fileName) =~ /^(84|85).xml$/)
  #(newlines in use field)
  #if (lc($fileName) =~ /10497.xml$/)
  #(Null entries)
  #if (lc($fileName) =~ /10984.xml$/)
  #Duplicate loans
  #if (lc($fileName) =~ /77320.xml$/)
  {
    #DEBUG
    #print "File name: ".$fileName."\n";
 
    #Parse file
    my $parser = XML::LibXML->new();
    my $tree = $parser->parse_file($fileName);
    my $root = $tree->getDocumentElement;
    
    foreach my $loan ($root->getElementsByTagName("loan"))
    {
      ###########################################################################
      #Get single elements
      #(Some if these items, such as id, may occur multiple times, but we only
      #want the first one)
      my ($id, $name, $status, $funded_amount, $activity, $sector, $use
        ,$country, $town, $level, $type, $pairs, $partner_id, $disbursal_amount
        ,$disbursal_currency, $disbursal_date, $loan_amount, $posted_date
        ,$funded_date, $paid_date, $refunded_date, $entries) =
      (
        getFirstChildData($loan->getElementsByTagName("id")),
        getFirstChildData($loan->getElementsByTagName("name")),
        getFirstChildData($loan->getElementsByTagName("status")),
        getFirstChildData($loan->getElementsByTagName("funded_amount")),
        getFirstChildData($loan->getElementsByTagName("activity")),
        getFirstChildData($loan->getElementsByTagName("sector")),
        getFirstChildData($loan->getElementsByTagName("use")),
        getFirstChildData($loan->getElementsByTagName("country")),
        getFirstChildData($loan->getElementsByTagName("town")),
        getFirstChildData($loan->getElementsByTagName("level")),
        getFirstChildData($loan->getElementsByTagName("type")),
        getFirstChildData($loan->getElementsByTagName("pairs")),
        getFirstChildData($loan->getElementsByTagName("partner_id")),
        getFirstChildData($loan->getElementsByTagName("disbursal_amount")),
        getFirstChildData($loan->getElementsByTagName("disbursal_currency")),
        getFirstChildData($loan->getElementsByTagName("disbursal_date")),
        getFirstChildData($loan->getElementsByTagName("loan_amount")),
        getFirstChildData($loan->getElementsByTagName("posted_date")),
        getFirstChildData($loan->getElementsByTagName("funded_date")),
        getFirstChildData($loan->getElementsByTagName("paid_date")),
        getFirstChildData($loan->getElementsByTagName("refunded_date")),
        getFirstChildData($loan->getElementsByTagName("entries"))
      );

      #Discontinue parsing if this is a duplicate loan
      if($id == $previousLoanID)
      {
        print "Duplicate loan not processed: $id\n";
        next;
      }
      $previousLoanID = $id;
      
      #To give some idea of how fast or slow it's going, display the ID every 100
      if($id % 100 == 0)
      {
        print "Processing id $id.\n";
      }


      ###########################################################################
      #Texts 
      #
      #Get the length of the first text (not including the HTML formatting)
      #There may be multiple texts, usually the first one is in the native language
      #The XML is <texts><[language1]>text</[language1]>...</texts>

      my @texts = $loan->getElementsByTagName("texts");
      my $textLength = 0;

      if(@texts == 0 or $texts[0] eq '' or $texts[0]->getFirstChild eq ''
        or $texts[0]->getFirstChild->getFirstChild eq '') { $textLength = 0; }
      else
      {
        #Remove all HTML formatting within the text
        my $temp = $texts[0]->getFirstChild->getFirstChild->getData;
        $temp =~ s/<.*?>//g;
        $textLength = length($temp);
      }
      #print "textLength = $textLength\n";



      ###########################################################################
      #Create Loan Insert statement
      $disbursal_date = convertDateToOracleFormat($disbursal_date);
      $posted_date = convertDateToOracleFormat($posted_date);
      $funded_date = convertDateToOracleFormat($funded_date);
      $paid_date = convertDateToOracleFormat($paid_date);
      $refunded_date = convertDateToOracleFormat($refunded_date);

      $name = convertStringToOracleFormat($name);
      $use = convertStringToOracleFormat($use);
      $country = convertStringToOracleFormat($country);
      $town = convertStringToOracleFormat($town);


        getFirstChildData($loan->getElementsByTagName("town")),

      
      #Split pairs into latitude and longitude.
      #THIS ASSUMES THAT ALL COORDINATES ARE POINTS!
      #The numbers are in the format [latitude] [longitude], so split them by the space
      #my $latitude = $pairs;
      #$latitude =~ s/(.*)\s.+/\1/;
      #my $longitude = $pairs;
      #$longitude =~ s/.+\s(.*)/\1/;

      $entries = convertNumberToOracleFormat($entries);
   
      my $loanInsert = "insert into loan values($id,'$name','$status'".
        ",$funded_amount,'$activity','$sector','$use','$country','$town'".
        ",'$level','$type','$pairs'".
        ",$partner_id,$disbursal_amount,'$disbursal_currency'".
        ",'$disbursal_date',$loan_amount,'$posted_date','$funded_date'".
        ",'$paid_date','$refunded_date',$entries,$textLength);";
      push(@loanInserts, $loanInsert);



   
      ###########################################################################
      #Payments CSV
      foreach my $payment ($loan->getElementsByTagName("payment"))
      {
        my ($amount, $local_amount, $processed_date, $settlement_date, $comment) =
        (
          getFirstChildData($payment->getElementsByTagName("amount")),
          getFirstChildData($payment->getElementsByTagName("local_amount")),
          getFirstChildData($payment->getElementsByTagName("processed_date")),
          getFirstChildData($payment->getElementsByTagName("settlement_date")),
          getFirstChildData($payment->getElementsByTagName("comment")),
        );

        #Remove html from comment
        $comment =~ s/<.*?>//g;

        $processed_date = convertDateToOracleFormat($processed_date);
        $settlement_date = convertDateToOracleFormat($settlement_date);

        my $paymentLine = "$id,$amount,$local_amount,$processed_date".
          ",$settlement_date,".length($comment);

        push(@paymentCSV, $paymentLine);
      }



      ###########################################################################
      #Local Payments CSV
      foreach my $localPayment ($loan->getElementsByTagName("local_payment"))
      {
        my ($due_date, $amount) =
        (
          getFirstChildData($localPayment->getElementsByTagName("due_date")),
          getFirstChildData($localPayment->getElementsByTagName("amount"))
        );

        $due_date = convertDateToOracleFormat($due_date);

        my $localPaymentLine = "$id,$due_date,$amount";
        push(@localPaymentCSV, $localPaymentLine);
      }


      ###########################################################################
      #Scheduled Payments
      foreach my $scheduledPayment ($loan->getElementsByTagName("scheduled_payment"))
      {
        my ($due_date, $amount) =
        (
          getFirstChildData($scheduledPayment->getElementsByTagName("due_date")),
          getFirstChildData($scheduledPayment->getElementsByTagName("amount"))
        );

        $due_date = convertDateToOracleFormat($due_date);

        my $scheduledPaymentLine = "$id,$due_date,$amount";
        push(@scheduledPaymentCSV, $scheduledPaymentLine);
      }


      ###########################################################################
      #Language
      foreach my $language ($loan->getElementsByTagName("language"))
      {
        $language = getFirstChildData($language);
        my $line = "$id,$language";
        push(@languageCSV, $line);
      }


      ###########################################################################
      #Borrowers
      foreach my $borrower ($loan->getElementsByTagName("borrower"))
      {
        my ($first_name, $last_name, $gender, $pictured) =
        (
          
          getFirstChildData($borrower->getElementsByTagName("first_name")),
          getFirstChildData($borrower->getElementsByTagName("last_name")),
          getFirstChildData($borrower->getElementsByTagName("gender")),
          getFirstChildData($borrower->getElementsByTagName("pictured"))
        );

        $first_name = convertStringToOracleFormat($first_name);
        $last_name = convertStringToOracleFormat($last_name);
        $pictured = convertNumberToOracleFormat($pictured);

        my $borrowerInsert = "insert into borrower values($id,'$first_name'".
          ",'$last_name','$gender',$pictured);";
        push(@borrowerInserts, $borrowerInsert);
      }



  

      #DEBUG
      #print "\n";
   
    } #End foreach loan
  } #End if XML file
} #End parseLoan
 
 






################################################################################
# Parse Partners
# Input 1: file name
################################################################################
sub parsePartners
{
  my $fileName = $_[0];
  my $parser = XML::LibXML->new();
  my $tree = $parser->parse_file($fileName);
  my $root = $tree->getDocumentElement;

  foreach my $partner ($root->getElementsByTagName("partner"))
  {
    my ($id, $name, $status, $rating, $start_date, $delinquency_rate
      ,$default_rate, $total_amount_raised, $loans_posted) =
    (
      getFirstChildData($partner->getElementsByTagName("id")),
      convertStringToOracleFormat(getFirstChildData($partner->getElementsByTagName("name"))),
      convertStringToOracleFormat(getFirstChildData($partner->getElementsByTagName("status"))),
      convertNumberToOracleFormat(getFirstChildData($partner->getElementsByTagName("rating"))),
      convertDateToOracleFormat(getFirstChildData($partner->getElementsByTagName("start_date"))),
      convertNumberToOracleFormat(getFirstChildData($partner->getElementsByTagName("delinquency_rate"))),
      convertNumberToOracleFormat(getFirstChildData($partner->getElementsByTagName("default_rate"))),
      convertNumberToOracleFormat(getFirstChildData($partner->getElementsByTagName("total_amount_raised"))),
      convertNumberToOracleFormat(getFirstChildData($partner->getElementsByTagName("loans_posted")))
    );

    my $partnerInsert = "insert into partner values($id,'$name','$status'".
      ",$rating,'$start_date',$delinquency_rate,$default_rate".
      ",$total_amount_raised,$loans_posted);";

    push(@partnerInserts, $partnerInsert);

    #DEBUG
    #print "$partnerInsert\n";
  }


  #Although the country information is stored for each partner, I don't actually
  #care about the mapping between partner and country, I'm more interested
  #in the country ISO code and region.
  foreach my $country ($root->getElementsByTagName("country"))
  {
    my ($iso_code, $region, $name) =
    (
      convertStringToOracleFormat(getFirstChildData($country->getElementsByTagName("iso_code"))),
      convertStringToOracleFormat(getFirstChildData($country->getElementsByTagName("region"))),
      convertStringToOracleFormat(getFirstChildData($country->getElementsByTagName("name")))
    );

    #Some of these inserts will be duplicates and will fail, but that shouldn't matter.
    my $countryInsert = "insert into country values('$name','$region','$iso_code');";
    push(@countryInserts, $countryInsert);

    #DEBUG
    #print "$countryInsert\n";
  }


}




################################################################################
# Print Inserts
#
# Print the SQL insert statements 
################################################################################
sub printInserts
{
  #DEBUG
  createInsertFile($baseDirectory."inserts/loanInserts.sql", \@loanInserts);
  createCSVFile($baseDirectory."inserts/payment.csv", \@paymentCSV);
  createCSVFile($baseDirectory."inserts/localPayment.csv", \@localPaymentCSV);
  createCSVFile($baseDirectory."inserts/scheduledPayment.csv", \@scheduledPaymentCSV);
  createInsertFile($baseDirectory."inserts/partnerInserts.sql", \@partnerInserts);
  createInsertFile($baseDirectory."inserts/countryInserts.sql", \@countryInserts);
  createCSVFile($baseDirectory."inserts/language.csv", \@languageCSV);
  createInsertFile($baseDirectory."inserts/borrowerInserts.sql", \@borrowerInserts);

}




################################################################################
#Create Insert File
#
#Create a file with an insert for each line.
#
#There are two different ways to insert data - insert statements and CSV files
#that will be used by SQL Loader.  Insert statments are much slower and take up 
#more space,but can handle UTF8. 
################################################################################
sub createInsertFile
{
  my $fileName = $_[0];
  my @lines = @{$_[1]};

  open MYFILE, ">:utf8", $fileName or die $!;

  #Faster and smaller to set format once instead of using to_date each time.
  print MYFILE "alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';\n";
  #We don't need to see millions of "1 row created"
  print MYFILE "set feedback off\n";
  #Prevent ampersands from being translated as variables by SQL*Plus
  print MYFILE "set define off\n\n";

  foreach my $line (@lines)
  {
    print MYFILE $line."\n";
  }
  close (MYFILE); 
}



################################################################################
# Create CSV File
#
# 
#
# sqlldr kiva/kiva inserts/language.ctl inserts/language.log inserts/language.bad inserts/language.csv
# sqlldr kiva/kiva inserts/payment.ctl inserts/payment.log inserts/payment.bad inserts/payment.csv
# sqlldr kiva/kiva inserts/localPayment.ctl inserts/localPayment.log inserts/localPayment.bad inserts/localPayment.csv
# sqlldr kiva/kiva inserts/scheduledPayment.ctl inserts/scheduledPayment.log inserts/scheduledPayment.bad inserts/scheduledPayment.csv

################################################################################
sub createCSVFile
{
  my $fileName = $_[0];
  my @lines = @{$_[1]};

  open MYFILE, ">:utf8", $fileName or die $!;

  foreach my $line (@lines)
  {
    print MYFILE $line."\n";
  }
  close (MYFILE); 
}
